<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <link rel="stylesheet" href="php-txt-db-api.css" type="text/css">
  <title>SQL Syntax (Einschr&auml;nkungen f&uuml;r Txt-Db-Api)</title>
</head>
<body>
<div align="center">
<h1 align="left"> SQL Syntax of the Txt-Db-Api (Limitations)</h1>
<h2 align="left">&nbsp;</h2>
<h2 align="left">Abstract</h2>
<p align="left">The Txt-Db-Api does not support all SQL
keywords/statements nor the SQL Standard.<br>
On this Page the sql syntax for the txt-db-api and its limitations are
listed.<br>
Everything in [] is optional, "..." means that any more elements can
follow, <br>
| means OR (AND|OR =&gt; you can put AND or OR at this place).</p>
<p align="left"><b>As in Standard SQL: Do always enclose values for str
(String) fields in quotes (' ' or " ") !</b></p>
<p align="left">&nbsp;</p>
<h2 align="left">SELECT</h2>
<h3 align="left">Syntax</h3>
<div align="left">
<pre align="left">SELECT [DISTINCT]<br>[function(][table_name.]column[)] [AS alias_name] [, [function(][table_name].column[)] [AS alias_name] , ...]  <br>FROM tablex [AS table_alias] [, tabley [AS table_alias], ... ] <br>[[LEFT|RIGHT|INNER] [OUTER] JOIN [table] ON table2, ... ] <br>[WHERE column=value AND|OR column=column AND|OR ...] <br>[GROUP BY column, ...]<br>[ORDER BY column [ASC | DESC] ,...]<br>[LIMIT [offset,]rows [;]      <br></pre>
<h3 align="left">Supported</h3>
<p>- look at the syntax...<br>
</p>
<p>- Instead of column names (<font face="Courier New, Courier, mono">column</font>
in the syntax above) you may also use direct values.<br>
&nbsp;&nbsp;This can be numbers or strings in ' ' or " ", for example <font
 face="Courier New, Courier, mono">SELECT 1234, 'bla', name FROM people;</font>
</p>
<p>- The following functions: UNIX_TIMESTAMP(), MD5(column), NOW(),
ABS(column), LCASE(column), UCASE(column), LOWER(column),
UPPER(column), EVAL(column
*-+/ value|column...)<br>
- The functions in the line above are also supported in the WHERE,
ORDER BY and GROUP BY part.<br>
- The following grouping functions: MAX(column), MIN(column),
COUNT(column), SUM(column), AVG(column)<br>
- You can also use direct values as parameters for the functions,
instead of columns, for example md5('bla')<br>
</p>
<p>- * instead of field names<br>
- table.*, combinations are also allowed, for example: SELECT table1.*,
people.Name, table3.* ...<br>
- SELECT COUNT([table.]*) <br>
</p>
<p>- Aliases for Tables</p>
<p>- In the WHERE part, the following operators are supported:
=,&gt;,&lt;,&gt;=,&lt;=, !=, &lt;&gt;, LIKE / NOT LIKE (you can use %
or _ as wildcards), IN / NOT IN <br>
- In the WHERE part, you can use field=value, value=value or
field=field conditions <br>
&nbsp;&nbsp;(to query tables linked, e.g. "person.typ_id =
types.id",&nbsp;for field=field conditions all the above <br>
&nbsp;&nbsp;listed operators can be used)<br>
- In the WHERE part you can use countless AND's and OR's<br>
- In the WHERE part brackets can be used, and also be nested<br>
</p>
<p>- JOIN's (INNER, LEFT [OUTER] and RIGHT [OUTER])<br>
- DISTINCT: only unique rows are returned<br>
- GROUP BY: the result will be grouped by this columns<br>
<br>
- LIMIT: only <i>rows </i>Rows are returned, starting with<i> offset
</i>(The first Row has offset 0!) <br>
<br>
- When using DISTINCT and GROUP BY always the first value is taken<br>
&nbsp;&nbsp;Example: "SELECT time, ip FROM log GROUP BY time"</p>
<table style="width: 608px; height: 90px;" border="0">
  <tbody>
    <tr>
      <td width="160">time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;ip<br>
10348395 | 192.168.0.1<br>
10348395 | 192.168.0.3</td>
      <td width="34">
      <div align="center">&gt;</div>
      </td>
      <td width="255">time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;ip<br>
10348395 | 192.168.0.1</td>
    </tr>
  </tbody>
</table>
<h3 align="left">Not supported</h3>
<p>- Appending of Fields like <i>SELECT name &amp; " " &amp; prename
AS whole_name</i> or<i><br>
&nbsp;&nbsp;SELECT name + prename AS whole_name</i><br>
- Everything not found under Syntax or supported !</p>
<h3>Examples</h3>
<p>SELECT prename, person.name, types.typ AS person_typ FROM person,
types<br>
WHERE person.typ_id = types.id AND (person.Nr&gt;=10 OR
person.prename='Hans') <br>
ORDER BY prename</p>
<p>SELECT COUNT(m.name), AVG(m.age) AS avgAge, c.name FROM members AS
m,
course AS c<br>
WHERE m.course_nr=course.id GROUP BY c.name;</p>
<p>SELECT md5('bla');<br>
</p>
<h3>Tip's</h3>
<p><b>ATTENTION: </b>Before and after each OR or AND must be a Space
(" "). This is<br>
requied for the parser, that can distinguish between field names
containing<br>
OR or AND as part of it, or real OR or AND's. The same applies for
LIKE.<br>
Examples:<br>
Wrong: Name='Test'OR Nr&gt;20<br>
Right: Name='Test' OR Nr&gt;20</p>
<p><b>ATTENTION: </b>Use LIKE only if it's really necessary. Normal
String compares are much<br>
faster then LIKE. For example, if you want to query all records where
the name is <b>exactly</b><br>
'tom', do it like this: SELECT * FROM table WHERE name='tom'<br>
AND NOT: SELECT * FROM table WHERE name LIKE 'tom'<br>
AND ALSO NOT: SELECT * FROM table WHERE name LIKE '%tom%'</p>
<p>&nbsp;</p>
<h2 align="left">INSERT</h2>
<h3 align="left">Syntax</h3>
<div align="left">
<pre align="left">INSERT [INTO] table [(column1, column2, column_n, ...)] <br>VALUES  (  [function(] value [)] [, [function(] value2 [)], value_n, ...]) [;]<br>	or<br>INSERT [INTO] table<br>SET column=[function(]value[)] [,column2= [function(]value2[)], column_n= [function(] value_n [)] , ...] [;]<br></pre>
<h3 align="left">Supported</h3>
<p>- Look at syntax<br>
- The following functions: UNIX_TIMESTAMP(), MD5(value), NOW(),
ABS(value), LCASE(value), UCASE(value), LOWER(value), UPPER(value),
EVAL(column *-+/ value|column...)<br>
- Only values can be used as parameters for the functions in INSERT
(an NOT columns)</p>
<h3 align="left">Not supported</h3>
- INSERT with SELECT<br>
- Insert more then one Record per SQL Statement<br>
- Grouping functions MAX(), MIN() etc.<br>
- Everything not under Syntax
<h3 align="left">Examples</h3>
<p>INSERT INTO person (prename, name) VALUES ('hans','meier');</p>
<p>INSERT INTO passwords (user, password) VALUES ( 'user1',
md5('mypassword') );</p>
<p>INSERT INTO person SET prename=upper('hans'), age=abs(-55),
added=NOW() ; </p>
<p>&nbsp;</p>
<h2 align="left">DELETE</h2>
<h3 align="left">Syntax</h3>
<div align="left">
<pre align="left">DELETE FROM table [WHERE &lt;same as in SELECT&gt;] [;]<br></pre>
<h3 align="left"><br>
Supported</h3>
<p>- Look at Syntax<br>
- Deleting the whole Table (drop the WHERE part, for example:
"DELETE FROM tableXY;")<br>
- Same WHERE Syntax as in SELECT (functions are also supported!)<br>
</p>
<h3 align="left">Not supported</h3>
<p>- LIMIT<br>
- Same restrictions for the WHERE part as by SELECT<br>
- Everything not found in the Syntax above..</p>
<h3>Examples</h3>
<p>DELETE FROM person WHERE prename='Hans';</p>
<p>DELETE FROM person WHERE password=md5('mypw') AND abs(age)=abs(-5);<br>
</p>
<p>&nbsp; </p>
<h2 align="left">UPDATE</h2>
<h3 align="left">Syntax</h3>
<div align="left">
<pre align="left">UPDATE table SET column=[function(]value[)] [,column2=[function(]value2[)], column_n=[function(]value_n[)], ...] <br>[WHERE &lt;same as in SELECT&gt;] [;]<br></pre>
<h3 align="left"><br>
Supported</h3>
<p>- Look at Syntax..<br>
- The following functions for the values: UNIX_TIMESTAMP(),
MD5(column or value), NOW(), ABS(column or value), LCASE(column
or value), <br>
&nbsp;&nbsp;UCASE(column or value), LOWER(column or value),
UPPER(column or value)<br>
- Update the whole Table (drop the WHERE part)<br>
- Assign the value of one column to another<br>
- Same syntax for the WHERE part as SELECT <br>
</p>
<h3 align="left">Not supported</h3>
<p>- LIMIT<br>
- ORDER BY<br>
- Everything not found in the Syntax above..</p>
<h3>Examples</h3>
<p>UPDATE person SET prename='Hans2000' WHERE prename='Hans';<br>
<br>
UPDATE person SET prename=name WHERE prename='Hans'; <br>
<br>
UPDATE person SET password=md5(password);<br>
<br>
UPDATE person SET age=abs(-20), name=upper(name); </p>
</div>
<p>&nbsp;</p>
</div>
<h2 align="left"><a name="createTable"></a>CREATE TABLE</h2>
<h3 align="left">Syntax</h3>
<div align="left">
<pre align="left">CREATE TABLE (column_name  type [DEFAULT 'defaultValue'] [, column_name2  type2 [DEFAULT 'defaultValue'] , ... ]) [;]<br></pre>
<h3 align="left"><br>
Supported</h3>
<p>- Look at Syntax<br>
- DEFAULT (Enclose the Default-Value in ' ')<br>
- ONLY the following Synatx: (column_name type , column_name type
, ....), and the Keyword DEFAULT, and thats all !<br>
- If you specify an Default-Value for an inc column, the first
records inc values will be set to DefaultValue+1,<br>
&nbsp;&nbsp;and for all other Records the Default-Value is ignored. <br>
</p>
<h3 align="left">Not supported</h3>
<p> - Everything not found under Syntax !!<br>
- SQL Standard types, <b>type can only be inc (Autoincrement),
int (Number) or str (String,Text).</b></p>
<h3>Example</h3>
<p>CREATE TABLE people (id inc, prename str DEFAULT 'john', name str);</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<h2 align="left">DROP TABLE</h2>
<h3 align="left">Syntax</h3>
<div align="left">
<pre align="left">DROP TABLE table1 [, table2, ...][;]</pre>
<h3 align="left"><br>
Supported</h3>
<p>- Look at Syntax<br>
- Drop 1 or more tables in one statement<br>
</p>
<h3 align="left">Not supported</h3>
<p> - Everything not found under Syntax !!<br>
</p>
<h3>Example</h3>
<p>DTOP TABLE people;</p>
<p>&nbsp;</p>
</div>
<h2 align="left">LIST TABLES</h2>
<h3 align="left">Syntax</h3>
<div align="left">
<pre align="left">LIST TABLES [WHERE ...] <br>[GROUP BY spalten_name, ...]<br>[ORDER BY spalten_name [ASC | DESC] ,...] <br>[LIMIT [offset,]anzahl] [;]      </pre>
<h3 align="left"><br>
Supported</h3>
<p>- Look at Syntax<br>
- Returns a ResultSet Object containing one Column with the
name "table", <br>
&nbsp;&nbsp;which contains all Table names of the Database as
String.<br>
- For the WHERE, GROUP BY, ORDER BY and LIMIT syntax see SELECT<br>
<br>
</p>
<h3 align="left">Not Supported</h3>
<p> - Everything not found under Syntax !!<br>
- Functions<br>
</p>
<h3>Example</h3>
<p>LIST TABLES;</p>
</div>
<p>&nbsp;</p>
<h2 align="left">CREATE DATABASE</h2>
<h3 align="left">Syntax</h3>
<div align="left">
<pre align="left">CREATE DATABASE database_name [;]</pre>
<h3 align="left"><br>
Supported</h3>
<p>- Look at Syntax<br>
- Only one Database can be created in one Statement.<br>
- This Statement should only be executed on a ROOT_DATABASE
Database!<br>
&nbsp;&nbsp;You can get an instance of it with a $db=new
Database(ROOT_DATABASE); call. <br>
&nbsp;&nbsp;ROOT_DATABASE is a defined constant.<br>
<br>
</p>
<h3 align="left">Not Supported</h3>
<p> - Everything not found under Syntax !!<br>
</p>
<h3>Example</h3>
<p>CREATE DATABASE myDatabase;</p>
</div>
<p>&nbsp;</p>
<h2 align="left">DROP DATABASE</h2>
<h3 align="left">Syntax</h3>
<div align="left">
<pre align="left">DROP DATABASE database_name [;]</pre>
<h3 align="left"><br>
Supported</h3>
<p>- Look at Syntax<br>
- Only one Database can be deleted in one Statement.<br>
- This Statement should only be executed on a ROOT_DATABASE
Database!<br>
&nbsp;&nbsp;You can get an instance of it with a $db=new
Database(ROOT_DATABASE); call. <br>
&nbsp;&nbsp;ROOT_DATABASE is a defined constant.<br>
</p>
<h3 align="left">Not Supported</h3>
<p> - Everything not found under Syntax !!<br>
</p>
<h3>Example</h3>
<p>DROP DATABASE myDatabase;</p>
</div>
<p>&nbsp;</p>
</div>
</div>
</div>
</div>
<br>
</body>
</html>
